﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;
using System.Data;
using System.Configuration;

namespace KPIS.GERP.GHRMS.DAL
{
    public class TrainingApproveDAL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public DataSet GetMsgSend(Nullable<int> send_seq)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(1);

            dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

            string sql = "SELECT ms.send_seq"
                        + " ,ms.send_date"
                        + " ,ms.ref_seq"
                        + " ,ms.ref_type"
                        + " ,ms.ref_no"
                        + " ,ms.send_text"
                        + " ,ms.from_emp_seq"
                        + " ,ms.to_emp_seq"
                        + " ,ms.from_send_seq"
                        + " ,ms.remarks"
                        + " ,ms.message_status"
                        + " ,concat( np.prefix_name,e.first_name,'  ',e.last_name) AS from_user"
                        + " ,concat( npp.prefix_name,ee.first_name,'  ',ee.last_name) AS to_use"
                        + " FROM message_send AS ms"
                        + " Left Join hrm_ps_employees AS e ON ms.from_emp_seq = e.emp_seq"
                        + " Left Join hrm_ps_employees AS ee ON ms.to_emp_seq = ee.emp_seq" 
                        + " Left Join mas_name_prefixes AS np ON e.name_prefix_seq = np.name_prefix_seq"
                        + " Left Join mas_name_prefixes AS npp ON ee.name_prefix_seq = npp.name_prefix_seq"
                        + " WHERE ms.record_status not like @delete_status "
                        + (send_seq != null ? " And ms.send_seq = send_seq" : "");

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;

        }

        public DataSet GetMsgReply(Nullable<int> reply_seq)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(1);

            dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

            string sql = "SELECT ms.reply_seq"
                        + " ,ms.reply_date"
                        //+ " ,ms.ref_seq"
                        + " ,ms.ref_type"
                        + " ,ms.ref_no"
                        + " ,ms.reply_text"
                        + " ,ms.from_emp_seq"
                        + " ,ms.to_emp_seq"
                        + " ,ms.from_reply_seq"
                        + " ,ms.remarks"
                        + " ,ms.message_status"
                        + " ,concat( np.prefix_name,e.first_name,'  ',e.last_name) AS from_user"
                        + " ,concat( npp.prefix_name,ee.first_name,'  ',ee.last_name) AS to_use"
                        + " FROM message_reply AS ms"
                        + " Left Join hrm_ps_employees AS e ON ms.from_emp_seq = e.emp_seq"
                        + " Left Join hrm_ps_employees AS ee ON ms.to_emp_seq = ee.emp_seq"
                        + " Left Join mas_name_prefixes AS np ON e.name_prefix_seq = np.name_prefix_seq"
                        + " Left Join mas_name_prefixes AS npp ON ee.name_prefix_seq = npp.name_prefix_seq"
                        + " WHERE ms.record_status not like @delete_status "
                        + (reply_seq != null ? " And ms.reply_seq = reply_seq" : "");

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }
        public void InsertMsgSend(int ref_seq, string ref_type, string send_text, int from_emp_seq, int to_emp_seq
                , Nullable<int> from_send_seq, string remarks, int created_by)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@send_date", DateTime.Now);
                dbManager.AddParameters(1, "@ref_seq", ref_seq);
                dbManager.AddParameters(2, "@ref_type", ref_type);
                dbManager.AddParameters(3, "@ref_no", "");
                dbManager.AddParameters(4, "@send_text", send_text);
                dbManager.AddParameters(5, "@from_emp_seq", from_emp_seq);
                dbManager.AddParameters(6, "@to_emp_seq", to_emp_seq);
                dbManager.AddParameters(7, "@from_send_seq", from_send_seq);
                dbManager.AddParameters(8, "@message_status", SystemInfo._ProcessRecordStatus);
                dbManager.AddParameters(9, "@remarks", remarks);
                dbManager.AddParameters(10, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(11, "@created_by", created_by);
                dbManager.AddParameters(12, "@created_when", DateTime.Today);

                strSQL = "INSERT INTO message_send"
                    + "(send_date"
                    + ", ref_seq"
                    + ", ref_type"
                    //+ ", ref_no"
                    + ", send_text"
                    + ", from_emp_seq"
                    + ", to_emp_seq"
                    + ", from_send_seq"
                    + ", message_status"
                    + ", remarks"
                    + ", record_status"
                    + ", created_by"
                    + ", created_when)"
                    + " VALUES (@send_date"
                    + ", @ref_seq"
                    + ", @ref_type"
                    //+ ", @ref_no"
                    + ", @send_text"
                    + ", @from_emp_seq"
                    + ", @to_emp_seq"
                    + ", @from_send_seq"
                    + ", @message_status"
                    + ", @remarks"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void InsertMsgReply(DateTime reply_date, string ref_type, string ref_no, string reply_text, int from_emp_seq, int to_emp_seq
                , int from_reply_seq, string message_status, string remarks, int created_by)//, int ref_seq
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(12);

                dbManager.AddParameters(0, "@reply_date", reply_date);
                //dbManager.AddParameters(1, "@ref_seq", ref_seq);
                dbManager.AddParameters(1, "@ref_type", ref_type);
                dbManager.AddParameters(2, "@ref_no", ref_no);
                dbManager.AddParameters(3, "@reply_text", reply_text);
                dbManager.AddParameters(4, "@from_emp_seq", from_emp_seq);
                dbManager.AddParameters(5, "@to_emp_seq", to_emp_seq);
                dbManager.AddParameters(6, "@from_reply_seq", from_reply_seq);
                dbManager.AddParameters(7, "@message_status", message_status);
                dbManager.AddParameters(8, "@remarks", remarks);
                dbManager.AddParameters(9, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(10, "@created_by", created_by);
                dbManager.AddParameters(11, "@created_when", DateTime.Today);

                strSQL = "INSERT INTO message_reply"
                    + "(reply_date"
                    //+ ", ref_seq"
                    + ", ref_type"
                    //+ ", ref_no"
                    + ", reply_text"
                    + ", from_emp_seq"
                    + ", to_emp_seq"
                    + ", from_reply_seq"
                    + ", message_status"
                    + ", remarks"
                    + ", record_status"
                    + ", created_by"
                    + ", created_when)"
                    + " VALUES (@reply_date"
                    //+ ", @ref_seq"
                    + ", @ref_type"
                    //+ ", @ref_no"
                    + ", @reply_text"
                    + ", @from_emp_seq"
                    + ", @to_emp_seq"
                    + ", @from_reply_seq"
                    + ", @message_status"
                    + ", @remarks"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void DeleteMsgSend(int send_seq, int updated_by)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@send_seq", send_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                strSQL = "UPDATE message_send"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE send_seq = @send_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void DeleteMsgReply(int reply_seq, int updated_by)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@reply_seq", reply_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                strSQL = "UPDATE message_reply"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE reply_seq = @reply_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataSet GetUpperPosition(Nullable<int> emp_seq)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(2);

            dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@emp_seq", emp_seq);
            

            string sql = "SELECT e.emp_seq, concat( np.prefix_name,e.first_name,'  ',e.last_name) AS full_name, p.pos_seq, p.pos_name"
                        + ", ee.first_name as upper_name, pp.pos_name as upper_pos_name"
                        + " FROM hrm_ps_employees e"
                        + " left join  hrm_mp_positions p on e.pos_seq = p.pos_seq"
                        + " left join  hrm_mp_positions pp on p.working_upper_pos_seq = pp.pos_seq"
                        + " left join hrm_ps_employees ee on pp.pos_seq = ee.pos_seq"
                        + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"
                        + " WHERE p.record_status not like @record_status "
                        + (emp_seq != null ? " And e.emp_seq = emp_seq" : "");

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }
    }
}
